/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package researchgrants.parts.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.SortedMap;
import java.util.TreeMap;
import java.util.logging.Level;
import java.util.logging.Logger;
import researchgrants.personalize.PropertyFile;

/**
 *
 * @author DOStudent1
 */
public class Db {
    static private Connection connGlobal=null;

    public static Connection openDbConnection() {
        // initially connects to the db
        if (connGlobal==null) {
            try {
//                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                // set this to a MS Access DB you have on your machine
//                String filename = "C:/Documents and Settings/DOStudent1/Desktop/Ohad/Research dept/Research.mdb";
                String filename = PropertyFile.getInstance().getDbFileName();
                String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
                database += filename.trim() + ";DriverID=22;READONLY=true}"; // add on to the end
                // now we can get the connection from the DriverManager

                connGlobal = DriverManager.getConnection(database, "ohad", "fordham");
            } catch (SQLException ex) {
                throw (new RuntimeException("Could not connect to DB: " + ex.getMessage()));
//            } catch (ClassNotFoundException ex) {
//                throw (new RuntimeException("Could not find JDBC-ODBC driver: " + ex.getMessage()));
            }
        }
        return(connGlobal);
    }

//    private static int count=0;
    public static PreparedStatement createPreparedStatement(Connection conn, String sqlString) throws SQLException {
//        Logger.getLogger(Db.class.getName()).log(Level.INFO, "Running db prepared query #" + Integer.toString(++count) + ":" + sqlString);
        return(conn.prepareStatement(sqlString));
    }
    
    public static void closeDbConnection(Connection conn) {
    }


    public static int getLastIdentity() throws SQLException {
        Connection conn=openDbConnection();
        PreparedStatement prepareStatement = Db.createPreparedStatement(conn, "SELECT @@IDENTITY");
        ResultSet resultSet = prepareStatement.executeQuery();
        try {
            moveToFirstLine(resultSet);
        } catch (Exception ex) {
            Logger.getLogger(Db.class.getName()).log(Level.SEVERE, null, ex);
        }
        int newId=resultSet.getInt(1);
        resultSet.close();
        prepareStatement.close();
        closeDbConnection(conn);
        return(newId);
    }


    public static void moveToFirstLine(ResultSet rs) throws Exception {
        if (rs==null) {
            throw(new Exception("No result found"));
        }
        if (!rs.next()) {
            throw(new Exception("No elements found"));
        }
    }

    public static Object getColumn(ResultSet rs, int column) throws Exception{
        Object res = rs.getObject(column);
        return(res);
    }

    public static Object getFirstColumn(ResultSet rs) throws Exception{
        return(getColumn(rs, 1));
    }

    public static Object getSingleRowSingleColumn(ResultSet rs) throws Exception {
        moveToFirstLine(rs);
        Object res=getColumn(rs, 1);
        if (rs.next()) {
            throw new Exception("More than one line found");
        }
        return(res);
    }
    
    public static Collection getFirstWholeColumn(ResultSet rs) throws SQLException {
        return(getSingleWholeColumn(rs, 1));
    }

    public static Collection<Object> getSingleWholeColumn(ResultSet rs, int columnNumber) throws SQLException {
        Collection<Object> res=new ArrayList<Object>();
        while (!rs.next()) {
            res.add(rs.getObject(columnNumber));
        }
        return(res);
    }
    
    public static SortedMap<String, String> getMap(ResultSet rs) throws SQLException {
        return(getMap(rs, 1,2));
    }

    private static SortedMap<String, String> getMap(ResultSet rs, int keysColumnNumber, int valuesColumnNumber) throws SQLException {
        SortedMap<String, String> res=new TreeMap<String, String>();
        while (rs.next()) {
            res.put(rs.getString(keysColumnNumber), rs.getString(valuesColumnNumber));
        }
        return(res);
    }

    static public void main(String[] args) {
        Connection conn;
//        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        // set this to a MS Access DB you have on your machine
//                String filename = "C:/Documents and Settings/DOStudent1/Desktop/Ohad/Research dept/Research.mdb";
        String filename = "src/researchgrants/resources/externals/Research.mdb";
        String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
        database += filename.trim() + ";DriverID=22;READONLY=true}"; // add on to the end
        // now we can get the connection from the DriverManager


        try {
            conn = DriverManager.getConnection(database, "ohad", "fordham");
            for (int i = 0; i < 100000; i++) {
                System.out.print("index" + Integer.toString(i) + "\n");
                Statement createStatement = conn.createStatement();
                createStatement.execute("SELECT SchoolName FROM tblSchools");
                ResultSet select=createStatement.getResultSet();
                select.close();
                createStatement.close();
            }
        } catch (Exception ex) {
            Logger.getLogger(Db.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    
}


